package test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class LoadExcel {
	/**
	 * @param args
	 * @throws IOException
	 */
	public void getExcelContentTest(String fileName) throws IOException {
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		boolean isExcel2003 = true;
		if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
			isExcel2003 = false;
		}
		File file = new File(fileName);
		FileInputStream inputStream = new FileInputStream(file);
		Workbook wb = isExcel2003 ? new HSSFWorkbook(inputStream)
				: new XSSFWorkbook(inputStream);
		// 读取第一章表格内容
		Sheet sheet = wb.getSheetAt(0);
		// 定义 row、cell
		Row row;
		String cell;
		// 循环输出表格中的内容
		for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum() + 1; i++) {
			row = sheet.getRow(i);
			if (row != null) {
				System.out.print("[第" + i + "行]" + "[共"
						+ row.getPhysicalNumberOfCells() + "列]" + "\t");
				for (int j = row.getFirstCellNum(); j < row
						.getPhysicalNumberOfCells(); j++) {
					cell = "";
					if (row.getCell(j) != null) {
						cell = row.getCell(j).toString();
					}
//					System.out.println(cell);
				}
			}
//			System.out.println("----------------------");
		}
	}
	/**
	 * 根据文件路径读取excel
	 *解析excel数据封装进list
	 * @param fileName
	 * @return
	 * @throws IOException
	 */
	public List<ArrayList<String>> getExcelContent(String fileName) throws IOException {
		List<ArrayList<String>> dataList = new ArrayList<ArrayList<String>>();
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		boolean isExcel2003 = true;
		if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
			isExcel2003 = false;
		}
		File file = new File(fileName);
		FileInputStream inputStream = new FileInputStream(file);
		Workbook wb = isExcel2003 ? new HSSFWorkbook(inputStream)
				: new XSSFWorkbook(inputStream);
		// 读取第一章表格内容
		Sheet sheet = wb.getSheetAt(0);
		// 定义 row、cell
		Row row;
		// 循环输出表格中的内容
//		System.out.println(sheet.getFirstRowNum()+"   \t"+sheet.getLastRowNum());
		for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum() + 1; i++) {
			row = sheet.getRow(i);
			if (row != null) {
				// 循环列
				ArrayList<String> rowList = new ArrayList<String>();
				//for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
				for (int j = row.getFirstCellNum(); j < 12; j++) {
					Cell cell = row.getCell(j);
					String cellValue = "";
					if (cell == null) {
						rowList.add(cellValue);
						continue;
					}
					/** 处理数字型的,自动去零 */
					if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
						/** 在excel里,日期也是数字,在此要进行判断 */
						if (HSSFDateUtil.isCellDateFormatted(cell)) {
							SimpleDateFormat simpleDateFormate = new SimpleDateFormat(
									"yyyy-MM-dd HH:mm:ss");
							cellValue = simpleDateFormate.format(cell
									.getDateCellValue());
						} else {
							cellValue =cell.getNumericCellValue()+ "";
						}
					}
					/** 处理字符串型 */
					else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
						cellValue = cell.getStringCellValue();
					}
					/** 处理布尔型 */
					else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
						cellValue = cell.getBooleanCellValue() + "";
					}
					/** 其它的,非以上几种数据类型 */
					else {
						cellValue = cell.toString() + "";
					}
					rowList.add(cellValue);
					
				}
				dataList.add(rowList);
			}
		}
		return dataList;
	}

	private String getRightStr(String sNum) {
		DecimalFormat decimalFormat = new DecimalFormat("#.00");
		String resultStr = decimalFormat.format(new Double(sNum));
		if (resultStr.matches("^[-+]?\\d+\\.[0]+$")) {
			resultStr = resultStr.substring(0, resultStr.indexOf("."));
		}
		return resultStr;
	}
	/**
	 * 解封装LIST 演示
	 * @param list
	 */
	public void listPrint(List<ArrayList<String>> list){
		for(int i=0;i<list.size();i++){
//			System.out.print("Line"+i+"\t");
//			System.out.print(list.get(i));
//			System.out.println("");
		}
	}
	public static void main(String[] args) {
		String fileName = "E:\\主材报价.xls";
		try {
			LoadExcel loadExcel = new LoadExcel();
			List<ArrayList<String>> list=loadExcel.getExcelContent(fileName);
//			loadExcel.listPrint(list);
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

}
